How to create Chart Visuals 11

Create HourCategory Column by following the steps below

Home >>>Transform >>>

Power Query>>>

Select Zendesk Table

Duplicate Created Column and Transform it to Hour>>>

Click Add column >>>>

Custom Column and Add the syntax below

if([Hour]) >7 and [Hour] < 11 then "7am-11am"             

else if([Hour]) > 11 and [Hour] <15 then "11am-3pm" 

else if([Hour])> 15 and [Hour] < 19 then "3pm-7pm"        

 else   "7pm-7am" 

Close and Apply




Follow the steps to build By Week chart visual

1. Drag and drop line and stacked column chart from visualizations.

Dimensions

General

X- 0,

Y-183,

Width -642,

Height 308

2. Drag and drop

·        shared axis

     For The Week and dayofweek from Calendar table

    column series

·         Hour category from  ZendeskTicketSystem  

     values 

·         Avg tickets per day 

      Line value

·         1stcontactresolutionRate 

3. Legend - on

·         Color - Black, 40% Lighter

·         Font family – Segoe UI

·         Text size - 9pt

·         Style - Markers only

4. X-axis - on

·         Color - Black

·         Text size - 9pt

·         Font family - Segoe UI

·         Title - off

5. Y-axis - on

·        Color - Black

·        Text size - 9pt

·        Font family - Segoe UI

·        Title - off

6. Secondary Y-axis - on

·        Color - Black

·        Text size - 9pt

·        Font family - Segoe UI

·        Title - off


7. Data colors 

·        1stContResolutionRate- #002050

·        7AM-11AM- #CDD800

·        11AM-3AM- #9C8521

·        3PM-7PM-   #F98D09

·        7PM-7PM-   #5A004F


Created a table that sorts the Color for Hour Category


Click on Hour and Sort by Order, Go to Model and create relationship with Zendesk table by Hour column

And Create a Calculated column on Zendesk Table to use it in the sort 

                   HourSort = RELATED('Sort Hour & Color'[Order])

8. Shapes -on

·         Shade area on

·         Stroke width -2

9. Title - on 

·        Title text: By Week

·        Font Color: White

·        Background Color: #005AA1

·        Alignment: Center

·        Text size: 12pt

10. Boarder - on

Sort the Week

Transfom and open the Power Query

Duplicate the Created date and Transfom it to

Start of the week and

Rename WeekStart

Close and apply

Open the Data view

Select For the Week Column

Sort by Weekstart

Enter

§  once you do the formatting follow the GIF below to filter the graph by last 12 weeks.



Follow the Steps below to build Open and New Tickets Visual 

1. Drag and drop line and stacked column chart from visualizations.

Dimensions 

General x-642, Y-183, Width -638, Height 308

2. Select the Agent Name column and Right click and New Group to rename Zendesk Ticket as New Ticket


Drag and drop

shared axis

·        Agent Name that is Created by the above Grouping

    values

·        Open tickets and new tickets Measures

     Line values 

·        Avg Days Open  measure

3. Legend - on

·        Color - Black, 40% Lighter

·        Font family – Segoe UI

·        Text size - 9pt

·        Style - Markers only

4. X-axis - on

·        Color - Black

·        Text size - 9pt

·        Font family - Segoe UI

·        Title - off

5. Y-axis - on

·        Color - Black

·        Text size - 12pt

·        Font family - Segoe UI

·        Title - off

6. Secondary Y-axis - on

·        Color - Black

·        Text size - 9pt

·        Font family - Segoe UI

·        Title - off

7. Data colors 

·        Avg days open - #5F3192

·        Open Tickets   - #B4009E

·        New Tickets     - #05F4F4

8. Shapes -on

·        Shade area on

·        Stroke width -2

9. Title - on 

·        Title text: Open and  New Ticket

·        Font Color: White

·        Background Color: #005AA1

·        Alignment: Center

·        Text size: 12pt

10. Boarder - on

·        Color - #E6E6E6

Expand the Filter and add the Last 12 Weeks filter calculated column into the Filter tab


Follow the Steps below to Avg Tickets per Day Visual

1. Drag and drop line and stacked column chart from visualizations.

Dimensions 

General x-0, Y-490, Width -643, Height 308

2. Drag and drop

·        shared axis

      Grouped Agent Name column from Zendesk table.

·        values

      Avg Tickets Per Day measure

     Line values

·        1stcontactresolutionRate  Measure

3. Legend - on

·        Color - Black, 40% Lighter

·        Font family – Segoe UI

·        Text size - 9pt

·        Style - Markers only

4. X-axis - on

·        Color - Black

·        Text size - 9pt

·        Font family - Segoe UI

·        Title - off

5. Y-axis - on

·        Color - Black

·        Text size - 12pt

·        Font family - Segoe UI

·        Title - off

6. Secondary Y-axis - on

·        Color - Black

·        Text size - 9pt

·        Font family - Segoe UI

·        Title - off

7. Data colors 

·        First Contact Resolution Rate - #002050

·        Default Color   - #CC7746

8. Shapes -on

·        Shade area on

·        Stroke width -2

9. Title - on 

·        Title text: Tickets per Day

·        Font Color: White

·        Background Color: #005AA1

·        Alignment: Center

·        Text size: 12pt

10. Boarder - on

·        Color - #E6E6E6

Add filter as shown below in the screenshot in the Filters tab


Follow the Steps below to build By Hour visual 

1. Drag and drop line an area chart from visualizations.

Dimension

General x-640, Y-490, Width -638, Height 308

2. Drag and drop

·      axis

 For The Week, dayofweek  from Calendar and Hour from Zendesk

·       values    

Average First Response hours measure

·         Line value

1stcontactresolutionRate measure

3. Legend - on

·         Color - Black

·         Font family – Segoe UI

·         Text size - 8pt

·         Style - Markers only

4. X-axis - on

·         Color - Black

·         Text size - 10pt

·         Font family - Segoe UI

·         Minimum category width-20

·         Maximum size-25%

·         Title - off

5. Y-axis - on

·        Color - Black

·        Text size - 12pt

·        Font family - Segoe UI

·        Title – off

o    Gridlines-on

·        Line color – white, 10% lighter

·        Line style- Solid

7. Secondary Y-axis - on

·        Color - Black

·        Text size - 12pt

·        Font family - Segoe UI

·        Title - off

8. Data colors 

·        AvgFirstRspHrs- #669396

·        1stConRRate - #002050

·        60% Area Transparency

9. Data Labels

·        1stConRRate Color – Black

·        Text Size – 9pt

·        AvgFirstRspHrs color- #669396     

·        Text Size – 9pt

10. Shapes -on

·         Stroke width -2

·         Join type – Round

·         Line style - solid    

11. Title - on 

·        Title text: Ticket performance by Hour

·        Font Color: White

·        Background Color: #005AA1

·        Alignment: Center

·        Text size: 12pt

·        • Font family - Segoe UI

12. Boarder - on

Add filter in the Filter tab to display last 12 weeks only


How to create line and stacked column chart?

https://www.youtube.com/watch?v=Pw1kSw3W66Q